mysql> insert into cliente(cedula,nombre,direccion,fechainicio) values('100','alexandra lopez','aranjuez','2003-12-03'),
-> ('200','sebastian perez','sabaneta','2005-07-17'),
-> ('300','sara herrera','floresta','2007-09-20'),
-> ('400','luisa roldan','floresta','2009-10-15'),
-> ('500','mario henao','bello','2004-02-28'),
-> ('600','luisa cifuentes','florencia','2003-09-01'),
-> ('700','edith garcia','envigado','2006-03-03'),
-> ('800','claudia lainez','itagui','2005-04-13'),
-> ('900','vivian piedrahita','centro','2006-01-19');
Query OK, 9 rows affected (0.55 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql> describe detallefactura;
+------------+--------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------------------+------+-----+---------+----------------+
| id | int(4) unsigned zerofill | NO | PRI | NULL | auto_increment |
| nrofactura | int(4) | NO | MUL | NULL | |
| codigo | char(10) | NO | MUL | NULL | |
| cantidad | int(4) | NO | | NULL | |
| valor | int(15) | NO | | NULL | |
| total | int(15) | NO | | NULL | |
+------------+--------------------------+------+-----+---------+----------------+
6 rows in set (0.27 sec)
mysql> insert into factura(nrofactura,cedula,subtotal,iva,retencion,total) values(1001,'100',0,0,0,0),
-> (2002,'300',0,0,0,0),
-> (3003,'200',0,0,0,0),
-> (4004,'400',0,0,0,0),
-> (5005,'200',0,0,0,0),
-> (6006,'500',0,0,0,0),
-> (7007,'100',0,0,0,0),
-> (8008,'400',0,0,0,0),
-> (9009,'400',0,0,0,0),
-> (1101,'600',0,0,0,0),
-> (2202,'700',0,0,0,0),
-> (3303,'600',0,0,0,0),
-> (4404,'100',0,0,0,0),
-> (5505,'700',0,0,0,0),
-> (6606,'800',0,0,0,0),
-> (7707,'900',0,0,0,0),
-> (8808,'800',0,0,0,0),
-> (9909,'900',0,0,0,0);
Query OK, 18 rows affected (0.17 sec)
Records: 18 Duplicates: 0 Warnings: 0
mysql> describe producto;
+---------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+----------+------+-----+---------+-------+
| codigo | char(10) | NO | PRI | NULL | |
| articulo | char(20) | NO | | NULL | |
| valorunitario | int(20) | NO | | NULL | |
| cantidad | int(4) | NO | | NULL | |
| valorventa | int(20) | NO | | NULL | |
| existencia | int(4) | NO | | NULL | |
+---------------+----------+------+-----+---------+-------+
6 rows in set (0.17 sec)
mysql> insert into producto(codigo,articulo,valorunitario,cantidad,valorventa,existencia) values
-> ('10','tv',1250000,22,0,0),
-> ('20','auriculares',75000,27,0,0),
-> ('30','mp3',150000,24,0,0),
-> ('40','mouse',25000,33,0,0),
-> ('50','teclado',130000,45,0,0),
-> ('60','disco duro',203000,17,0,0),
-> ('70','unidad dvd',250000,19,0,0),
-> ('80','usb',80000,28,0,0),
-> ('90','lapiz optico',134000,17,0,0);
Query OK, 9 rows affected (0.20 sec)
Records: 9 Duplicates: 0 Warnings: 0
mysql> insert into detallefactura(nrofactura,codigo,cantidad,valor,total) values
-> (1001,'10',1,0,0),
-> (1001,'40',2,0,0),
-> (1001,'70',1,0,0),
-> (2002,'60',2,0,0),
-> (3003,'20',4,0,0),
-> (3003,'80',1,0,0),
-> (4004,'10',2,0,0),
-> (4004,'20',1,0,0),
-> (4004,'30',1,0,0),
-> (4004,'60',1,0,0),
-> (4004,'70',1,0,0),
-> (4004,'80',2,0,0),
-> (5005,'10',3,0,0),
-> (6006,'20',1,0,0),
-> (6006,'80',2,0,0),
-> (6006,'30',2,0,0),
-> (6006,'90',2,0,0),
-> (6006,'50',1,0,0),
-> (6006,'40',2,0,0),
-> (7007,'10',1,0,0),
-> (7007,'90',2,0,0),
-> (8008,'60',2,0,0),
-> (8008,'40',2,0,0),
-> (8008,'50',1,0,0),
-> (9009,'50',2,0,0),
-> (9009,'80',1,0,0),
-> (1101,'30',1,0,0),
-> (2202,'30',2,0,0),
-> (1101,'60',1,0,0),
-> (3303,'60',2,0,0),
-> (4404,'90',3,0,0),
-> (5505,'80',4,0,0),
-> (6606,'70',2,0,0),
-> (7707,'10',1,0,0),
-> (8808,'40',2,0,0),
-> (9909,'20',3,0,0),
-> (9909,'30',4,0,0),
-> (6606,'50',5,0,0),
-> (7707,'40',1,0,0),
-> (1101,'30',1,0,0);
Query OK, 40 rows affected (0.16 sec)
Records: 40 Duplicates: 0 Warnings: 0
mysql> update producto set valorventa=(select valorunitario +(valorunitario*0.17));
Query OK, 9 rows affected (0.09 sec)
Rows matched: 9 Changed: 9 Warnings: 0
mysql> select * from producto;
+--------+--------------+---------------+----------+------------+------------+
| codigo | articulo | valorunitario | cantidad | valorventa | existencia |
+--------+--------------+---------------+----------+------------+------------+
| 10 | tv | 1250000 | 22 | 1462500 | 0 |
| 20 | auriculares | 75000 | 27 | 87750 | 0 |
| 30 | mp3 | 150000 | 24 | 175500 | 0 |
| 40 | mouse | 25000 | 33 | 29250 | 0 |
| 50 | teclado | 130000 | 45 | 152100 | 0 |
| 60 | disco duro | 203000 | 17 | 237510 | 0 |
| 70 | unidad dvd | 250000 | 19 | 292500 | 0 |
| 80 | usb | 80000 | 28 | 93600 | 0 |
| 90 | lapiz optico | 134000 | 17 | 156780 | 0 |
+--------+--------------+---------------+----------+------------+------------+
9 rows in set (0.00 sec)
mysql> update detallefactura set valor=(select valorventa from producto where producto.codigo=detallefactura.codigo);
Query OK, 40 rows affected (0.27 sec)
Rows matched: 40 Changed: 40 Warnings: 0
mysql> select * from detallefactura;
+------+------------+--------+----------+---------+-------+
| id | nrofactura | codigo | cantidad | valor | total |
+------+------------+--------+----------+---------+-------+
| 0081 | 1001 | 10 | 1 | 1462500 | 0 |
| 0082 | 1001 | 40 | 2 | 29250 | 0 |
| 0083 | 1001 | 70 | 1 | 292500 | 0 |
| 0084 | 2002 | 60 | 2 | 237510 | 0 |
| 0085 | 3003 | 20 | 4 | 87750 | 0 |
| 0086 | 3003 | 80 | 1 | 93600 | 0 |
| 0087 | 4004 | 10 | 2 | 1462500 | 0 |
| 0088 | 4004 | 20 | 1 | 87750 | 0 |
| 0089 | 4004 | 30 | 1 | 175500 | 0 |
| 0090 | 4004 | 60 | 1 | 237510 | 0 |
| 0091 | 4004 | 70 | 1 | 292500 | 0 |
| 0092 | 4004 | 80 | 2 | 93600 | 0 |
| 0093 | 5005 | 10 | 3 | 1462500 | 0 |
| 0094 | 6006 | 20 | 1 | 87750 | 0 |
| 0095 | 6006 | 80 | 2 | 93600 | 0 |
| 0096 | 6006 | 30 | 2 | 175500 | 0 |
| 0097 | 6006 | 90 | 2 | 156780 | 0 |
| 0098 | 6006 | 50 | 1 | 152100 | 0 |
| 0099 | 6006 | 40 | 2 | 29250 | 0 |
| 0100 | 7007 | 10 | 1 | 1462500 | 0 |
| 0101 | 7007 | 90 | 2 | 156780 | 0 |
| 0102 | 8008 | 60 | 2 | 237510 | 0 |
| 0103 | 8008 | 40 | 2 | 29250 | 0 |
| 0104 | 8008 | 50 | 1 | 152100 | 0 |
| 0105 | 9009 | 50 | 2 | 152100 | 0 |
| 0106 | 9009 | 80 | 1 | 93600 | 0 |
| 0107 | 1101 | 30 | 1 | 175500 | 0 |
| 0108 | 2202 | 30 | 2 | 175500 | 0 |
| 0109 | 1101 | 60 | 1 | 237510 | 0 |
| 0110 | 3303 | 60 | 2 | 237510 | 0 |
| 0111 | 4404 | 90 | 3 | 156780 | 0 |
| 0112 | 5505 | 80 | 4 | 93600 | 0 |
| 0113 | 6606 | 70 | 2 | 292500 | 0 |
| 0114 | 7707 | 10 | 1 | 1462500 | 0 |
| 0115 | 8808 | 40 | 2 | 29250 | 0 |
| 0116 | 9909 | 20 | 3 | 87750 | 0 |
| 0117 | 9909 | 30 | 4 | 175500 | 0 |
| 0118 | 6606 | 50 | 5 | 152100 | 0 |
| 0119 | 7707 | 40 | 1 | 29250 | 0 |
| 0120 | 1101 | 30 | 1 | 175500 | 0 |
+------+------------+--------+----------+---------+-------+
40 rows in set (0.00 sec)
mysql> update detallefactura set total=cantidad*valor;
Query OK, 40 rows affected (0.16 sec)
Rows matched: 40 Changed: 40 Warnings: 0
mysql> select * from detallefactura;
+------+------------+--------+----------+---------+---------+
| id | nrofactura | codigo | cantidad | valor | total |
+------+------------+--------+----------+---------+---------+
| 0081 | 1001 | 10 | 1 | 1462500 | 1462500 |
| 0082 | 1001 | 40 | 2 | 29250 | 58500 |
| 0083 | 1001 | 70 | 1 | 292500 | 292500 |
| 0084 | 2002 | 60 | 2 | 237510 | 475020 |
| 0085 | 3003 | 20 | 4 | 87750 | 351000 |
| 0086 | 3003 | 80 | 1 | 93600 | 93600 |
| 0087 | 4004 | 10 | 2 | 1462500 | 2925000 |
| 0088 | 4004 | 20 | 1 | 87750 | 87750 |
| 0089 | 4004 | 30 | 1 | 175500 | 175500 |
| 0090 | 4004 | 60 | 1 | 237510 | 237510 |
| 0091 | 4004 | 70 | 1 | 292500 | 292500 |
| 0092 | 4004 | 80 | 2 | 93600 | 187200 |
| 0093 | 5005 | 10 | 3 | 1462500 | 4387500 |
| 0094 | 6006 | 20 | 1 | 87750 | 87750 |
| 0095 | 6006 | 80 | 2 | 93600 | 187200 |
| 0096 | 6006 | 30 | 2 | 175500 | 351000 |
| 0097 | 6006 | 90 | 2 | 156780 | 313560 |
| 0098 | 6006 | 50 | 1 | 152100 | 152100 |
| 0099 | 6006 | 40 | 2 | 29250 | 58500 |
| 0100 | 7007 | 10 | 1 | 1462500 | 1462500 |
| 0101 | 7007 | 90 | 2 | 156780 | 313560 |
| 0102 | 8008 | 60 | 2 | 237510 | 475020 |
| 0103 | 8008 | 40 | 2 | 29250 | 58500 |
| 0104 | 8008 | 50 | 1 | 152100 | 152100 |
| 0105 | 9009 | 50 | 2 | 152100 | 304200 |
| 0106 | 9009 | 80 | 1 | 93600 | 93600 |
| 0107 | 1101 | 30 | 1 | 175500 | 175500 |
| 0108 | 2202 | 30 | 2 | 175500 | 351000 |
| 0109 | 1101 | 60 | 1 | 237510 | 237510 |
| 0110 | 3303 | 60 | 2 | 237510 | 475020 |
| 0111 | 4404 | 90 | 3 | 156780 | 470340 |
| 0112 | 5505 | 80 | 4 | 93600 | 374400 |
| 0113 | 6606 | 70 | 2 | 292500 | 585000 |
| 0114 | 7707 | 10 | 1 | 1462500 | 1462500 |
| 0115 | 8808 | 40 | 2 | 29250 | 58500 |
| 0116 | 9909 | 20 | 3 | 87750 | 263250 |
| 0117 | 9909 | 30 | 4 | 175500 | 702000 |
| 0118 | 6606 | 50 | 5 | 152100 | 760500 |
| 0119 | 7707 | 40 | 1 | 29250 | 29250 |
| 0120 | 1101 | 30 | 1 | 175500 | 175500 |
+------+------------+--------+----------+---------+---------+
40 rows in set (0.00 sec)
mysql> update factura set subtotal=(select sum(total) from detallefactura where detallefactura.nrofactura=factura.nrofactura);
Query OK, 18 rows affected (0.16 sec)
Rows matched: 18 Changed: 18 Warnings: 0
mysql> select * from factura;
+------------+--------+----------+-----+-----------+-------+
| nrofactura | cedula | subtotal | iva | retencion | total |
+------------+--------+----------+-----+-----------+-------+
| 1001 | 100 | 1813500 | 0 | 0 | 0 |
| 1101 | 600 | 588510 | 0 | 0 | 0 |
| 2002 | 300 | 475020 | 0 | 0 | 0 |
| 2202 | 700 | 351000 | 0 | 0 | 0 |
| 3003 | 200 | 444600 | 0 | 0 | 0 |
| 3303 | 600 | 475020 | 0 | 0 | 0 |
| 4004 | 400 | 3905460 | 0 | 0 | 0 |
| 4404 | 100 | 470340 | 0 | 0 | 0 |
| 5005 | 200 | 4387500 | 0 | 0 | 0 |
| 5505 | 700 | 374400 | 0 | 0 | 0 |
| 6006 | 500 | 1150110 | 0 | 0 | 0 |
| 6606 | 800 | 1345500 | 0 | 0 | 0 |
| 7007 | 100 | 1776060 | 0 | 0 | 0 |
| 7707 | 900 | 1491750 | 0 | 0 | 0 |
| 8008 | 400 | 685620 | 0 | 0 | 0 |
| 8808 | 800 | 58500 | 0 | 0 | 0 |
| 9009 | 400 | 397800 | 0 | 0 | 0 |
| 9909 | 900 | 965250 | 0 | 0 | 0 |
+------------+--------+----------+-----+-----------+-------+
18 rows in set (0.00 sec)
mysql> update factura set iva=(subtotal*0.16), retencion=(subtotal*0.035), total=(subtotal+iva-retencion);
Query OK, 18 rows affected (0.11 sec)
Rows matched: 18 Changed: 18 Warnings: 0
mysql> select * from factura;
+------------+--------+----------+--------+-----------+---------+
| nrofactura | cedula | subtotal | iva | retencion | total |
+------------+--------+----------+--------+-----------+---------+
| 1001 | 100 | 1813500 | 290160 | 63473 | 2040187 |
| 1101 | 600 | 588510 | 94162 | 20598 | 662074 |
| 2002 | 300 | 475020 | 76003 | 16626 | 534397 |
| 2202 | 700 | 351000 | 56160 | 12285 | 394875 |
| 3003 | 200 | 444600 | 71136 | 15561 | 500175 |
| 3303 | 600 | 475020 | 76003 | 16626 | 534397 |
| 4004 | 400 | 3905460 | 624874 | 136691 | 4393643 |
| 4404 | 100 | 470340 | 75254 | 16462 | 529132 |
| 5005 | 200 | 4387500 | 702000 | 153563 | 4935937 |
| 5505 | 700 | 374400 | 59904 | 13104 | 421200 |
| 6006 | 500 | 1150110 | 184018 | 40254 | 1293874 |
| 6606 | 800 | 1345500 | 215280 | 47093 | 1513687 |
| 7007 | 100 | 1776060 | 284170 | 62162 | 1998068 |
| 7707 | 900 | 1491750 | 238680 | 52211 | 1678219 |
| 8008 | 400 | 685620 | 109699 | 23997 | 771322 |
| 8808 | 800 | 58500 | 9360 | 2048 | 65812 |
| 9009 | 400 | 397800 | 63648 | 13923 | 447525 |
| 9909 | 900 | 965250 | 154440 | 33784 | 1085906 |
+------------+--------+----------+--------+-----------+---------+
18 rows in set (0.00 sec)
mysql> update producto set existencia=cantidad-(select sum(cantidad) from detallefactura where detallefactura.codigo=producto.codigo);
Query OK, 9 rows affected (0.53 sec)
Rows matched: 9 Changed: 9 Warnings: 0
mysql> select * from producto;
+--------+--------------+---------------+----------+------------+------------+
| codigo | articulo | valorunitario | cantidad | valorventa | existencia |
+--------+--------------+---------------+----------+------------+------------+
| 10 | tv | 1250000 | 22 | 1462500 | 14 |
| 20 | auriculares | 75000 | 27 | 87750 | 18 |
| 30 | mp3 | 150000 | 24 | 175500 | 13 |
| 40 | mouse | 25000 | 33 | 29250 | 24 |
| 50 | teclado | 130000 | 45 | 152100 | 36 |
| 60 | disco duro | 203000 | 17 | 237510 | 9 |
| 70 | unidad dvd | 250000 | 19 | 292500 | 15 |
| 80 | usb | 80000 | 28 | 93600 | 18 |
| 90 | lapiz optico | 134000 | 17 | 156780 | 10 |
+--------+--------------+---------------+----------+------------+------------+
9 rows in set (0.00 sec)
mysql> select cedula, count(cedula) as total_factura from factura group by cedula;
+--------+---------------+
| cedula | total_factura |
+--------+---------------+
| 100 | 3 |
| 200 | 2 |
| 300 | 1 |
| 400 | 3 |
| 500 | 1 |
| 600 | 2 |
| 700 | 2 |
| 800 | 2 |
| 900 | 2 |
+--------+---------------+
9 rows in set (0.08 sec)
mysql> select codigo, sum(total) as total_venta from detallefactura group by codigo;
+--------+-------------+
| codigo | total_venta |
+--------+-------------+
| 10 | 11700000 |
| 20 | 789750 |
| 30 | 1930500 |
| 40 | 263250 |
| 50 | 1368900 |
| 60 | 1900080 |
| 70 | 1170000 |
| 80 | 936000 |
| 90 | 1097460 |
+--------+-------------+
9 rows in set (0.00 sec)
mysql> select cliente.nombre, factura.cedula, count(factura.cedula) as total_factura from factura inner join cliente on factura.cedula=cliente.cedula group by factura.cedula;
+-------------------+--------+---------------+
| nombre | cedula | total_factura |
+-------------------+--------+---------------+
| alexandra lopez | 100 | 3 |
| sebastian perez | 200 | 2 |
| sara herrera | 300 | 1 |
| luisa roldan | 400 | 3 |
| mario henao | 500 | 1 |
| luisa cifuentes | 600 | 2 |
| edith garcia | 700 | 2 |
| claudia lainez | 800 | 2 |
| vivian piedrahita | 900 | 2 |
+-------------------+--------+---------------+
9 rows in set (0.03 sec)
mysql> select producto.articulo, detallefactura.codigo, sum(detallefactura.total) as total_venta from detallefactura inner join producto on detallefactura.codigo=producto.codigo group by detallefactura.codigo;
+--------------+--------+-------------+
| articulo | codigo | total_venta |
+--------------+--------+-------------+
| tv | 10 | 11700000 |
| auriculares | 20 | 789750 |
| mp3 | 30 | 1930500 |
| mouse | 40 | 263250 |
| teclado | 50 | 1368900 |
| disco duro | 60 | 1900080 |
| unidad dvd | 70 | 1170000 |
| usb | 80 | 936000 |
| lapiz optico | 90 | 1097460 |
+--------------+--------+-------------+
9 rows in set (0.00 sec)
mysql> select cliente.nombre, factura.nrofactura, factura.total from factura inner join cliente on factura.cedula=cliente.cedula group by cliente.nombre having cliente.nombre='alexandra lopez';
+-----------------+------------+---------+
| nombre | nrofactura | total |
+-----------------+------------+---------+
| alexandra lopez | 1001 | 2040187 |
+-----------------+------------+---------+
1 row in set (0.00 sec)
mysql> select cliente.nombre, factura.nrofactura, factura.total from factura inner join cliente on factura.cedula=cliente.cedula group by factura.nrofactura having cliente.nombre='alexandra lopez';
+-----------------+------------+---------+
| nombre | nrofactura | total |
+-----------------+------------+---------+
| alexandra lopez | 1001 | 2040187 |
| alexandra lopez | 4404 | 529132 |
| alexandra lopez | 7007 | 1998068 |
+-----------------+------------+---------+
3 rows in set (0.00 sec)
mysql> describe cliente;
+-------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| cedula | int(10) | NO | PRI | NULL | |
| nombre | char(40) | NO | | NULL | |
| direccion | char(30) | NO | | NULL | |
| fechainicio | date | NO | | NULL | |
+-------------+----------+------+-----+---------+-------+
4 rows in set (0.09 sec)
mysql> describe factura;
+------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| nrofactura | int(4) | NO | PRI | NULL | |
| cedula | int(10) | NO | MUL | NULL | |
| subtotal | int(20) | NO | | NULL | |
| iva | int(20) | NO | | NULL | |
| retencion | int(20) | NO | | NULL | |
| total | int(20) | NO | | NULL | |
+------------+---------+------+-----+---------+-------+
6 rows in set (0.09 sec)
mysql> describe producto;
+---------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+----------+------+-----+---------+-------+
| codigo | char(10) | NO | PRI | NULL | |
| articulo | char(20) | NO | | NULL | |
| valorunitario | int(20) | NO | | NULL | |
| cantidad | int(4) | NO | | NULL | |
| valorventa | int(20) | NO | | NULL | |
| existencia | int(4) | NO | | NULL | |
+---------------+----------+------+-----+---------+-------+
6 rows in set (0.03 sec)
mysql> create table detallado(id int(11) auto_increment not null primary key,
-> cedula int(10) not null,
-> nrofactura int(4) not null,
-> totalarticulos int(4) not null,
-> valortotal int(20) not null);
Query OK, 0 rows affected (0.55 sec)
mysql> describe detallado;
+----------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| cedula | int(10) | NO | | NULL | |
| nrofactura | int(4) | NO | | NULL | |
| totalarticulos | int(4) | NO | | NULL | |
| valortotal | int(20) | NO | | NULL | |
+----------------+---------+------+-----+---------+----------------+
5 rows in set (0.19 sec)
mysql> insert into detallado select detallefactura.id, factura.cedula, factura.nrofactura, detallefactura.cantidad, factura.total from factura inner join detallefactura on factura.nrofactura=detallefactura.nrofactura group by factura.nrofactura;
Query OK, 18 rows affected (0.08 sec)
Records: 18 Duplicates: 0 Warnings: 0
mysql> select * from detallado;
+-----+--------+------------+----------------+------------+
| id | cedula | nrofactura | totalarticulos | valortotal |
+-----+--------+------------+----------------+------------+
| 81 | 100 | 1001 | 1 | 2040187 |
| 84 | 300 | 2002 | 2 | 534397 |
| 85 | 200 | 3003 | 4 | 500175 |
| 87 | 400 | 4004 | 2 | 4393643 |
| 93 | 200 | 5005 | 3 | 4935937 |
| 94 | 500 | 6006 | 1 | 1293874 |
| 100 | 100 | 7007 | 1 | 1998068 |
| 102 | 400 | 8008 | 2 | 771322 |
| 105 | 400 | 9009 | 2 | 447525 |
| 107 | 600 | 1101 | 1 | 662074 |
| 108 | 700 | 2202 | 2 | 394875 |
| 110 | 600 | 3303 | 2 | 534397 |
| 111 | 100 | 4404 | 3 | 529132 |
| 112 | 700 | 5505 | 4 | 421200 |
| 113 | 800 | 6606 | 2 | 1513687 |
| 114 | 900 | 7707 | 1 | 1678219 |
| 115 | 800 | 8808 | 2 | 65812 |
| 116 | 900 | 9909 | 3 | 1085906 |
+-----+--------+------------+----------------+------------+
18 rows in set (0.00 sec)
mysql> delete from factura where cedula=(select cedula from cliente where year(fechainicio)<2004 and cliente.cedula=factura.cedula group by factura.cedula);
Query OK, 5 rows affected (0.09 sec)
mysql> select * from factura;
+------------+--------+----------+--------+-----------+---------+
| nrofactura | cedula | subtotal | iva | retencion | total |
+------------+--------+----------+--------+-----------+---------+
| 2002 | 300 | 475020 | 76003 | 16626 | 534397 |
| 2202 | 700 | 351000 | 56160 | 12285 | 394875 |
| 3003 | 200 | 444600 | 71136 | 15561 | 500175 |
| 4004 | 400 | 3905460 | 624874 | 136691 | 4393643 |
| 5005 | 200 | 4387500 | 702000 | 153563 | 4935937 |
| 5505 | 700 | 374400 | 59904 | 13104 | 421200 |
| 6006 | 500 | 1150110 | 184018 | 40254 | 1293874 |
| 6606 | 800 | 1345500 | 215280 | 47093 | 1513687 |
| 7707 | 900 | 1491750 | 238680 | 52211 | 1678219 |
| 8008 | 400 | 685620 | 109699 | 23997 | 771322 |
| 8808 | 800 | 58500 | 9360 | 2048 | 65812 |
| 9009 | 400 | 397800 | 63648 | 13923 | 447525 |
| 9909 | 900 | 965250 | 154440 | 33784 | 1085906 |
+------------+--------+----------+--------+-----------+---------+
13 rows in set (0.00 sec)
mysql> select cliente.nombre, factura.nrofactura from cliente left join factura on cliente.cedula=factura.cedula where factura.cedula is null;
+-----------------+------------+
| nombre | nrofactura |
+-----------------+------------+
| alexandra lopez | NULL |
| luisa cifuentes | NULL |
+-----------------+------------+
2 rows in set (0.00 sec)
mysql> exit